package com.sxj.corejava.code12_jdbc;

import com.sxj.corejava.util.DataAccessException;
import com.sxj.corejava.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 石小俊
 * @date 2024年07月02日 15:43
 * 两张表
 * 班级表:主键、班级号(唯一不为空)、所属专业
 * 学生表:主键、学号、姓名、年龄、所属班级(外键)
 * 1.保存班级以及该班中的所有学生信息
 * 2.根据班级号查询指定班级以及该班级中所有学生信息
 * 3.查询所有学生以及其所属班级信息
 */
public class Test09_学生管理系统 {

    public static void main(String[] args) throws DataAccessException {
//        ClassBean classBean = new ClassBean();
//        classBean.setNo("jsj002");
//        classBean.setMajor("软件开发");
//
//        List<Student> students = new ArrayList<>();
//        Student student = new Student("zj004", "lucy", 20, classBean);
//        Student student2 = new Student("zj005", "tom", 19, classBean);
//        Student student3 = new Student("zj006", "jack", 18, classBean);
//        students.add(student);
//        students.add(student2);
//        students.add(student3);
//        try {
//            insert(classBean, students);
//            System.out.println("恭喜您,添加成功!");
//        } catch (DataAccessException e) {
//            e.printStackTrace();
//            System.out.println("对不起,添加失败,失败原因:" + e.getMessage());
//        }

//        ClassBean classBean = findByNo("jsj001");
//        System.out.println(classBean);

        List<Student> students = findAll();
        for(Student student : students){
            System.out.println(student);
        }
    }

    /**
     * 保存班级以及该班中的所有学生信息
     * @param classBean
     * @param students
     * @throws DataAccessException
     */
    public static void insert(ClassBean classBean, List<Student> students) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;

        try {
            conn = JDBCUtil.getConnection();
            // 先保存班级,保存班级后返回对应的主键
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append("   (no,major)")
                    .append(" values ")
                    .append("   (?,?)")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, classBean.getNo());
            ps.setString(2, classBean.getMajor());
            ps.executeUpdate();
            // 获取返回的主键
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            // 将返回的主键交给班级对象
            classBean.setId(id);

            // 准备保存学生,重新获取状态集
            sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append("   (no,name,age,class_id)")
                    .append(" values ")
                    .append("   (?,?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            // 存在多个学生
            for (Student student : students) {
                // 每个学生对应的状态集参数都不一样
                // 遍历操作,每次遍历一个学生,设置对应学生的状态集参数
                ps.setString(1, student.getNo());
                ps.setString(2, student.getName());
                ps.setInt(3, student.getAge());
                ps.setInt(4, student.getClassBean().getId());
                // 执行一次DML操作
                ps.executeUpdate();
            }

        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }

    }

    /**
     * 根据班级号查询指定班级以及该班级中所有学生信息
     * @param no
     * @return
     */
    public static ClassBean findByNo(String no) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ClassBean classBean = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id 's.id',s.no 's.no',s.name,s.age ")
                    .append(" from t_class c ")
                    .append(" left join t_student s ")
                    .append("   on c.id = s.class_id ")
                    .append(" where c.no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            rs = ps.executeQuery();
            while (rs.next()) {
                // 一方数据映射
                if (classBean == null) {
                    classBean = new ClassBean();
                    classBean.setId(rs.getInt("id"));
                    classBean.setNo(rs.getString("no"));
                    classBean.setMajor(rs.getString("major"));
                }

                // 多方数据映射
                Student student = new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                classBean.addStudent(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return classBean;
    }

    /**
     * 查询所有学生以及其所属班级信息
     * @return
     */
    public static List<Student> findAll() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();

        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id 'c.id',c.no 'c.no',c.major ")
                    .append(" from t_student s ")
                    .append(" left join t_class c ")
                    .append("   on s.class_id = c.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));

                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("major"));

                student.setClassBean(classBean);

                students.add(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

}
